IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetClinicalTrialsPIList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetClinicalTrialsPIList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetClinicalTrialsPIList]
(
	@domainUserID int,
	@moduleRoleBitmask int
)
as
begin
	set nocount on

	--The list of PIs consists of PIs that are on resources that the user has
	--who have access to via porfolios, organization, and direct access.
	IF OBJECT_ID('tempdb..#piList') IS NOT NULL
				drop table #piList
	CREATE TABLE #piList(AdmPersonId int)
	
	declare @createNewBudget int; set @createNewBudget = 16384
	declare @budgetAdministrator int; set @budgetAdministrator = 131072
	declare @budgetType int; set @budgetType = 3
	declare @primayEmailType int; set @primayEmailType = 1

	-- Get the list of relevant PIs based on the budget resources the user has access to. 
	--insert into #piList
	declare @adminRole int
	SELECT @adminRole = ModuleRoleBitmask 
					 from FwkApplicationUser
					 WHERE FwkDomainUserId = @domainUserID
	
    IF ((@adminRole & @budgetAdministrator) = @budgetAdministrator)
	BEGIN
		insert into #piList
			select distinct af.PrincipalInvestigatorId
			from AgrAgreement_Fct af
			
		insert into #piList
			select distinct p.PIPersonId
			from Protocol p			
			where p.IsCurrent = 1
	END
	ELSE
	BEGIN
		INSERT INTO #piList
		SELECT DISTINCT	ap.AdmPersonId
		from AdmPortfolio ap
		inner join AdmRights_Map arm
		on ap.AdmRightsMapId = arm.Id
		inner join FwkDomainUser du
		on du.Id = arm.FwkDomainUserId
		WHERE du.Id = @domainUserID
		AND (arm.RightBitmask &  @createNewBudget) = @createNewBudget
	END

	CREATE INDEX piIndex on #piList(AdmPersonId)

	--  Build the result set based on the PI lists generated above.
	select
		ap.[Id],
		(ltrim(rtrim(case patindex('%,%', ap.LastName) when 0 then ap.LastName else substring(ap.LastName, 0, patindex('%,%', ap.LastName)) end)) + ', ' +
		ltrim(rtrim(case patindex('%,%', ap.FirstName) when 0 then ap.FirstName else substring(ap.FirstName, 0, patindex('%,%', ap.FirstName)) end)) +
		case when len(ltrim(rtrim(isnull(ap.MiddleName, '')))) = 0 then '' else ' ' + ltrim(rtrim(ap.MiddleName)) end) as PIName,
		max(ltrim(rtrim(isnull(aea.EmailAddress, '')))) as EMail
	from
		AdmPerson ap
		inner join #piList pil on pil.AdmPersonID = ap.[Id]
		left outer join AdmEmailAddress aea on aea.AdmPersonId = ap.[Id] and aea.AdmEmailAddressTypeId = @primayEmailType
	group by
		ap.[Id],
		(ltrim(rtrim(case patindex('%,%', ap.LastName) when 0 then ap.LastName else substring(ap.LastName, 0, patindex('%,%', ap.LastName)) end)) + ', ' +
		ltrim(rtrim(case patindex('%,%', ap.FirstName) when 0 then ap.FirstName else substring(ap.FirstName, 0, patindex('%,%', ap.FirstName)) end)) +
		case when len(ltrim(rtrim(isnull(ap.MiddleName, '')))) = 0 then '' else ' ' + ltrim(rtrim(ap.MiddleName)) end)
	order by
		PIName

DROP TABLE #piList
end